use anyhow::Result;
use once_cell::sync::OnceCell;
use regex::Regex;
use serde::{Deserialize, Serialize};
use serde_json::Value as JsonValue;
use std::collections::HashMap;
use std::fs;
use std::path::{Path, PathBuf};
use tracing::info;

#[derive(Debug, Clone, Serialize, Deserialize)]
pub enum StatementKind {
    Select,
    Insert,
    Update,
    Delete,
}

#[derive(Debug, Clone)]
pub struct Statement {
    pub id: String,                // namespace.id
    pub sql: String,               // raw sql text
    pub kind: StatementKind,
    pub param_names: Vec<String>,  // extracted from #{param}
    pub nodes: Option<Vec<Node>>,  // parsed dynamic SQL nodes (if/foreach)
}

#[derive(Default, Debug, Clone)]
pub struct SqlRegistry {
    pub stmts: HashMap<String, Statement>,
}

impl SqlRegistry {
    pub fn get(&self, id: &str) -> Option<&Statement> {
        self.stmts.get(id)
    }

    pub fn prepare(&self, id: &str, params: &HashMap<String, String>) -> Option<(String, Vec<String>)> {
        let stmt = self.get(id)?;
        if let Some(nodes) = &stmt.nodes {
            let json_params = json_from_hash(params);
            let mut out_sql = String::new();
            let mut out_params: Vec<String> = Vec::new();
            render_nodes(nodes, &json_params, &mut out_sql, &mut out_params).ok()?;
            Some((normalize_sql(&out_sql), out_params))
        } else {
            // Replace #{name} with ? and collect values in order
            let re = Regex::new(r#"#\{([a-zA-Z_][a-zA-Z0-9_]*)\}"#).unwrap();
            let mut ordered: Vec<String> = Vec::new();
            let sql = re
                .replace_all(&stmt.sql, |caps: &regex::Captures| {
                    let name = caps.get(1).unwrap().as_str().to_string();
                    ordered.push(params.get(&name).cloned().unwrap_or_default());
                    "?".to_string()
                })
                .to_string();
            Some((normalize_sql(&sql), ordered))
        }
    }

    pub fn prepare_json(&self, id: &str, params: &JsonValue) -> Option<(String, Vec<String>)> {
        let stmt = self.get(id)?;
        if let Some(nodes) = &stmt.nodes {
            let mut out_sql = String::new();
            let mut out_params: Vec<String> = Vec::new();
            render_nodes(nodes, params, &mut out_sql, &mut out_params).ok()?;
            Some((normalize_sql(&out_sql), out_params))
        } else {
            // Fallback: static replacement using top-level string map
            let map = params.as_object()?;
            let sparams: HashMap<String, String> = map
                .iter()
                .map(|(k, v)| (k.clone(), json_to_string(v)))
                .collect();
            self.prepare(id, &sparams)
        }
    }
}

static GLOBAL_REGISTRY: OnceCell<SqlRegistry> = OnceCell::new();

pub fn init_global_from_dir_once(dir: impl AsRef<Path>) -> Result<()> {
    if GLOBAL_REGISTRY.get().is_some() {
        return Ok(());
    }
    let reg = load_from_dir(dir)?;
    let _ = GLOBAL_REGISTRY.set(reg);
    Ok(())
}

pub fn global_registry() -> Option<&'static SqlRegistry> {
    GLOBAL_REGISTRY.get()
}

pub fn load_from_dir(dir: impl AsRef<Path>) -> Result<SqlRegistry> {
    let mut reg = SqlRegistry::default();
    let dir = dir.as_ref();
    let mut files: Vec<PathBuf> = Vec::new();
    for entry in fs::read_dir(dir)? {
        let entry = entry?;
        let path = entry.path();
        if path.extension().map(|e| e == "xml").unwrap_or(false) {
            files.push(path);
        }
    }
    for file in files {
        let text = fs::read_to_string(&file)?;
        let mut ns = String::new();
        // Extract mapper namespace
    if let Some(caps) = Regex::new(r#"<mapper[^>]*?namespace=\"([^\"]+)\"[^>]*>"#)
            .unwrap()
            .captures(&text)
        {
            ns = caps.get(1).unwrap().as_str().to_string();
        }
        // Extract statements without using backreferences (not supported by Rust regex)
        // select
        for caps in Regex::new(r#"(?is)<select\s+id=\"([^\"]+)\"[^>]*>(.*?)</select>"#).unwrap().captures_iter(&text) {
            let id_local = caps.get(1).unwrap().as_str().to_string();
            let body = caps.get(2).unwrap().as_str().to_string();
            let kind = StatementKind::Select;
            let param_re = Regex::new(r#"#\{([a-zA-Z_][a-zA-Z0-9_]*)\}"#).unwrap();
            let mut names: Vec<String> = Vec::new();
            for m in param_re.captures_iter(&body) {
                names.push(m.get(1).unwrap().as_str().to_string());
            }
            let nodes = if body.contains("<if") || body.contains("<foreach") {
                Some(parse_nodes_from_body(&body)?)
            } else {
                None
            };
            let full_id = if ns.is_empty() { id_local.clone() } else { format!("{}.{}", ns, id_local) };
            reg.stmts.insert(
                full_id.clone(),
                Statement { id: full_id, sql: body, kind, param_names: names, nodes },
            );
        }
        // insert
        for caps in Regex::new(r#"(?is)<insert\s+id=\"([^\"]+)\"[^>]*>(.*?)</insert>"#).unwrap().captures_iter(&text) {
            let id_local = caps.get(1).unwrap().as_str().to_string();
            let body = caps.get(2).unwrap().as_str().to_string();
            let kind = StatementKind::Insert;
            let param_re = Regex::new(r#"#\{([a-zA-Z_][a-zA-Z0-9_]*)\}"#).unwrap();
            let mut names: Vec<String> = Vec::new();
            for m in param_re.captures_iter(&body) {
                names.push(m.get(1).unwrap().as_str().to_string());
            }
            let nodes = if body.contains("<if") || body.contains("<foreach") {
                Some(parse_nodes_from_body(&body)?)
            } else {
                None
            };
            let full_id = if ns.is_empty() { id_local.clone() } else { format!("{}.{}", ns, id_local) };
            reg.stmts.insert(
                full_id.clone(),
                Statement { id: full_id, sql: body, kind, param_names: names, nodes },
            );
        }
        // update
        for caps in Regex::new(r#"(?is)<update\s+id=\"([^\"]+)\"[^>]*>(.*?)</update>"#).unwrap().captures_iter(&text) {
            let id_local = caps.get(1).unwrap().as_str().to_string();
            let body = caps.get(2).unwrap().as_str().to_string();
            let kind = StatementKind::Update;
            let param_re = Regex::new(r#"#\{([a-zA-Z_][a-zA-Z0-9_]*)\}"#).unwrap();
            let mut names: Vec<String> = Vec::new();
            for m in param_re.captures_iter(&body) {
                names.push(m.get(1).unwrap().as_str().to_string());
            }
            let nodes = if body.contains("<if") || body.contains("<foreach") {
                Some(parse_nodes_from_body(&body)?)
            } else {
                None
            };
            let full_id = if ns.is_empty() { id_local.clone() } else { format!("{}.{}", ns, id_local) };
            reg.stmts.insert(
                full_id.clone(),
                Statement { id: full_id, sql: body, kind, param_names: names, nodes },
            );
        }
        // delete
        for caps in Regex::new(r#"(?is)<delete\s+id=\"([^\"]+)\"[^>]*>(.*?)</delete>"#).unwrap().captures_iter(&text) {
            let id_local = caps.get(1).unwrap().as_str().to_string();
            let body = caps.get(2).unwrap().as_str().to_string();
            let kind = StatementKind::Delete;
            let param_re = Regex::new(r#"#\{([a-zA-Z_][a-zA-Z0-9_]*)\}"#).unwrap();
            let mut names: Vec<String> = Vec::new();
            for m in param_re.captures_iter(&body) {
                names.push(m.get(1).unwrap().as_str().to_string());
            }
            let nodes = if body.contains("<if") || body.contains("<foreach") {
                Some(parse_nodes_from_body(&body)?)
            } else {
                None
            };
            let full_id = if ns.is_empty() { id_local.clone() } else { format!("{}.{}", ns, id_local) };
            reg.stmts.insert(
                full_id.clone(),
                Statement { id: full_id, sql: body, kind, param_names: names, nodes },
            );
        }
    }
    info!(count = reg.stmts.len(), "Loaded XML SQL mappers");
    Ok(reg)
}

#[derive(Debug, Clone, Default)]
pub struct NoopExecutor;

impl NoopExecutor {
    pub fn execute(&self, sql: &str, params: &[String]) -> Result<()> {
        info!(sql = %sql, params = ?params, "Noop execute");
        Ok(())
    }
}

// ---------------- Dynamic SQL AST & Rendering ----------------

#[derive(Debug, Clone)]
pub enum Node {
    Text(String),
    Param(String),
    If { test: String, children: Vec<Node> },
    Foreach {
        collection: String,
        item: String,
        open: Option<String>,
        close: Option<String>,
        separator: Option<String>,
        children: Vec<Node>,
    },
}

fn normalize_sql(s: &str) -> String {
    let s = s.replace('\n', " ");
    let re = Regex::new(r"\s+").unwrap();
    re.replace_all(&s, " ").trim().to_string()
}

fn json_to_string(v: &JsonValue) -> String {
    match v {
        JsonValue::Null => "".to_string(),
        JsonValue::Bool(b) => b.to_string(),
        JsonValue::Number(n) => n.to_string(),
        JsonValue::String(s) => s.clone(),
        _ => v.to_string(),
    }
}

fn json_from_hash(map: &HashMap<String, String>) -> JsonValue {
    let m: serde_json::Map<String, JsonValue> = map
        .iter()
        .map(|(k, v)| (k.clone(), JsonValue::String(v.clone())))
        .collect();
    JsonValue::Object(m)
}

fn parse_nodes_from_body(body: &str) -> Result<Vec<Node>> {
    let wrapped = format!("<root>{}</root>", body);
    let mut reader = quick_xml::Reader::from_str(&wrapped);
    reader.trim_text(true);
    let mut buf = Vec::new();
    let mut stack: Vec<Vec<Node>> = vec![Vec::new()];
    // Hold current element attributes for if/foreach
    #[derive(Debug, Clone)]
    enum FrameKind { If { test: String }, Foreach { collection: String, item: String, open: Option<String>, close: Option<String>, separator: Option<String> } }
    let mut frames: Vec<FrameKind> = Vec::new();

    let param_re = Regex::new(r#"#\{([a-zA-Z_][a-zA-Z0-9_]*)\}"#).unwrap();

    loop {
        use quick_xml::events::Event;
        match reader.read_event_into(&mut buf)? {
            Event::Start(e) => {
                let name = String::from_utf8_lossy(e.name().as_ref()).to_string();
                if name == "if" {
                    let mut test = String::new();
                    for a in e.attributes().with_checks(false) {
                        let a = a?;
                        if a.key.as_ref() == b"test" {
                            test = a.unescape_value()?.into_owned();
                        }
                    }
                    frames.push(FrameKind::If { test });
                    stack.push(Vec::new());
                } else if name == "foreach" {
                    let mut collection = String::new();
                    let mut item = String::from("item");
                    let mut open = None;
                    let mut close = None;
                    let mut separator = None;
                    for a in e.attributes().with_checks(false) {
                        let a = a?;
                        match a.key.as_ref() {
                            b"collection" => collection = a.unescape_value()?.into_owned(),
                            b"item" => item = a.unescape_value()?.into_owned(),
                            b"open" => open = Some(a.unescape_value()?.into_owned()),
                            b"close" => close = Some(a.unescape_value()?.into_owned()),
                            b"separator" => separator = Some(a.unescape_value()?.into_owned()),
                            _ => {}
                        }
                    }
                    frames.push(FrameKind::Foreach { collection, item, open, close, separator });
                    stack.push(Vec::new());
                } else {
                    // Unknown tags: we don't create a frame; just start a group to collect children
                    stack.push(Vec::new());
                }
            }
            Event::Text(t) => {
                let txt = t.unescape()?.to_string();
                // Split into text/param nodes
                let mut last = 0;
                for caps in param_re.captures_iter(&txt) {
                    let m = caps.get(0).unwrap();
                    if m.start() > last {
                        stack.last_mut().unwrap().push(Node::Text(txt[last..m.start()].to_string()));
                    }
                    let name = caps.get(1).unwrap().as_str().to_string();
                    stack.last_mut().unwrap().push(Node::Param(name));
                    last = m.end();
                }
                if last < txt.len() {
                    stack.last_mut().unwrap().push(Node::Text(txt[last..].to_string()));
                }
            }
            Event::End(e) => {
                let name = String::from_utf8_lossy(e.name().as_ref()).to_string();
                if let Some(frame) = frames.pop() {
                    let children = stack.pop().unwrap();
                    match frame {
                        FrameKind::If { test } => {
                            if name == "if" {
                                stack.last_mut().unwrap().push(Node::If { test, children });
                            } else {
                                // unknown tag: inline children
                                stack.last_mut().unwrap().extend(children);
                            }
                        }
                        FrameKind::Foreach { collection, item, open, close, separator } => {
                            if name == "foreach" {
                                stack.last_mut().unwrap().push(Node::Foreach { collection, item, open, close, separator, children });
                            } else {
                                stack.last_mut().unwrap().extend(children);
                            }
                        }
                    }
                } else {
                    // Unknown tag end: just inline collected children
                    let children = stack.pop().unwrap_or_default();
                    stack.last_mut().unwrap().extend(children);
                }
            }
            Event::Eof => break,
            _ => {}
        }
        buf.clear();
    }
    Ok(stack.pop().unwrap_or_default())
}

fn eval_test(expr: &str, params: &JsonValue) -> bool {
    // Very simple evaluator: supports `a != null`, `a == null`, `a != ''`, `a == ''`, combined with `and`/`or`
    let tokens: Vec<&str> = expr.split_whitespace().collect();
    // Parse sequence: term (and|or term)*
    fn term(tok: &[&str], params: &JsonValue) -> (bool, usize) {
        if tok.len() < 3 { return (false, tok.len()); }
        let left = tok[0];
        let op = tok[1];
        let right = tok[2];
        let v = lookup(params, left);
        let res = match (op, right) {
            ("!=", "null") => !v.is_null(),
            ("==", "null") => v.is_null(),
            ("!=", "''") | ("!=", "\"\"") => json_to_string(&v) != "",
            ("==", "''") | ("==", "\"\"") => json_to_string(&v) == "",
            _ => false,
        };
        (res, 3)
    }
    let mut idx = 0;
    let mut acc = false;
    let mut first = true;
    while idx < tokens.len() {
        let (tval, consumed) = term(&tokens[idx..], params);
        if first { acc = tval; first = false; } else { acc = tval; }
        idx += consumed;
        if idx < tokens.len() {
            let op = tokens[idx];
            idx += 1;
            let (tval2, consumed2) = term(&tokens[idx..], params);
            match op {
                "and" => acc = acc && tval2,
                "or" => acc = acc || tval2,
                _ => {}
            }
            idx += consumed2;
        }
    }
    acc
}

fn lookup<'a>(params: &'a JsonValue, path: &str) -> &'a JsonValue {
    // only top-level keys supported for now
    params.get(path).unwrap_or(&JsonValue::Null)
}

fn render_nodes(nodes: &[Node], params: &JsonValue, out_sql: &mut String, out_params: &mut Vec<String>) -> Result<()> {
    for n in nodes {
        match n {
            Node::Text(t) => out_sql.push_str(t),
            Node::Param(name) => {
                out_sql.push_str("?");
                out_params.push(json_to_string(lookup(params, name)));
            }
            Node::If { test, children } => {
                if eval_test(test, params) {
                    render_nodes(children, params, out_sql, out_params)?;
                }
            }
            Node::Foreach { collection, item, open, close, separator, children } => {
                let arr = lookup(params, collection);
                if let JsonValue::Array(items) = arr {
                    if let Some(o) = open { out_sql.push_str(o); }
                    for (i, it) in items.iter().enumerate() {
                        let mut local = params.clone();
                        if let JsonValue::Object(mut obj) = local {
                            obj.insert(item.clone(), it.clone());
                            local = JsonValue::Object(obj);
                        }
                        render_nodes(children, &local, out_sql, out_params)?;
                        if i + 1 < items.len() {
                            if let Some(sep) = separator { out_sql.push_str(sep); }
                        }
                    }
                    if let Some(c) = close { out_sql.push_str(c); }
                }
            }
        }
    }
    Ok(())
}